"""
    数据库访问
"""
import sqlite3

# 1、连接数据库
conn = sqlite3.connect('/Users/wangyucai/Desktop/demo.db')

# 2、创建一个游标，用于处理数据
cur = conn.cursor()

# 3、建表
sql = """
    CREATE TABLE sys_user (
	    id INTEGER PRIMARY KEY AUTOINCREMENT,
	    user_name TEXT,
	    user_code TEXT, 
	    total_score NUMERIC, 
	    grade TEXT);
"""
count = cur.execute(sql)

# 4、插入数据
sql = """
    INSERT INTO sys_user (user_name, user_code, total_score, grade)
        VALUES('张三', 'jk-001', 399, '21级');
"""
count = cur.execute(sql)
conn.commit()

# 5、插入多条数据
dataList = [
    ('李四', 'jk-002', 388, '21级'),
    ('王武', 'jk-003', 377, '22级')
]
cur.executemany('INSERT INTO sys_user (user_name, user_code, total_score, grade) values(?,?,?,?);', dataList)
conn.commit()

# 6、更新数据
sql = """
    UPDATE sys_user SET user_name='张三1' WHERE id=1;
"""
try:
    cur.execute(sql)
    conn.commit()
except:
    conn.rollback()

# 7、查询数据
sql = "select user_name, user_code, total_score, grade from sys_user;"
cur.execute(sql)
resList = cur.fetchall()
for row in resList:
    user_name, user_code, total_score, grade = row
    print("name={}, code={}, score={}, grade={}".format(user_name, user_code, total_score, grade))

# 8、删除数据
sql = "DELETE FROM sys_user WHERE id=3;"
cur.execute(sql)
conn.commit()

# 9、关闭游标和连接
cur.close()
conn.close()

# ===========================================================
# 其他数据库
# ---------mysql------------
# https://github.com/PyMySQL/PyMySQL
# pip install PyMySQL
"""
    import pymysql
    conn = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
"""
# ---------postgresql------------
# pip install psycopg2
"""
    import psycopg2
    conn = psycopg2.connect(database="test",
                        user="postgres",
                        password="postgres",
                        host="127.0.0.1",
                        port="5432")
"""
# ---------Sqlserver------------
# 微软帮助： https://learn.microsoft.com/zh-cn/sql/connect/python/pymssql/step-1-configure-development-environment-for-pymssql-python-development?view=sql-server-ver16
# 文件名：pymssql‑2.2.4‑cp310‑cp310‑win_amd64.whl
# pip install pymssql‑2.2.4‑cp310‑cp310‑win_amd64.whl
"""
    import pymssql  
    conn = pymssql.connect(host='127.0.0.1', 
                    user='sa', 
                    password='123456', 
                    database='test')  
"""

# ======正式的写法================================================
conn = sqlite3.connect('/Users/wangyucai/Desktop/demo.db')

try:
    cur = conn.cursor()
    sql = """
        INSERT INTO sys_user (user_name, user_code, total_score, grade)
            VALUES('张三', 'jk-001', 399, '21级');
    """
    cur.execute(sql)
except:
    conn.rollback()
else:
    conn.commit()
    cur.close()




cur = conn.cursor()
sql = "select user_name, user_code, total_score, grade from sys_user;"
cur.execute(sql)
resList = cur.fetchall()
for row in resList:
    user_name, user_code, total_score, grade = row
    print("name={}, code={}, score={}, grade={}".format(user_name, user_code, total_score, grade))

# ==========请自行查询python数据库连接池的使用=======================
